package com.sqltojson.dao;

import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.cxf.common.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.sqltojson.core.Parameter;
import com.sqltojson.core.Query;
import com.sqltojson.core.Resultat;
import com.sqltojson.core.Row;
import com.sqltojson.exception.QueryDaoException;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

/**
 * Execution de requete.
 */
public final class ExecuteQueryDao {

	/** Le logger.	 */
	private static final Logger LOGGER = LoggerFactory
			.getLogger(ExecuteQueryDao.class);


	/** La configuration pour les templates.*/
	private final Configuration configuration;
	/** La datasource permettant d'executer les requetes.*/
	private DataSource dataSource;

	/**
	 * Initialise la configuration du template.
	 */
	public ExecuteQueryDao() {
		configuration = new Configuration();
	}

	/**
	 * @param query
	 *            la requete
	 * @param map
	 *            la map des valeurs pour les parametres de la requete
	 * @return le resultat de la requete
	 * @throws QueryDaoException
	 *             en cas d'erreur lors de l'execution de la requete.
	 */
	public Resultat find(final Query query, final Map<String, String> map)
			throws QueryDaoException {
		LOGGER.info("maps {}", map);
		Connection connection = null;
		Statement preparatemnetS = null;
		ResultSet resultSet = null;
		Resultat result = null;
		try {
			// recuperation de la connexion
			connection = dataSource.getConnection();
			final Map<String, String> model = Maps.newHashMap();
			final List<Parameter> forSql = Lists.newArrayList();
			if(query.getParameter() != null){
				for (final Parameter parameter : query.getParameter()) {
					if (parameter.getTemplate() != null && parameter.getTemplate()) {
						final String foundValue = foundValue(parameter,
								map.get(parameter.getName()));
						model.put(parameter.getName(), foundValue);
						if(parameter.getAssociate() != null) {
							forSql.add(parameter);
						}

					} else {
						forSql.add(parameter);
					}
				}
			}
		
			preparatemnetS = createStatementAndExecute(query, model, map,
					connection, forSql, 0);

			// creation du resultat.
			resultSet = preparatemnetS.getResultSet();
			result = new Resultat();
			if (resultSet != null) {
				final ResultSetMetaData metadata = resultSet.getMetaData();
				final int columnCount = metadata.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					result.addColumnName(metadata.getColumnName(i));
				}
				while (resultSet.next()) {
					result.add(create(resultSet));
				}
			}

		} catch (final SQLException e) {
			LOGGER.error("erreur", e);
			throw new QueryDaoException(e.getMessage());
		} catch (final IOException e) {
			throw new QueryDaoException(e.getMessage());
		} catch (final TemplateException e) {
			throw new QueryDaoException(e.getMessage());
		} finally {
			closeAll(connection, preparatemnetS, resultSet);
		}

		return result;
	}

	private Statement createStatementAndExecute(final Query query,
			final Map<String, String> model, final Map<String, String> map,
			final Connection connection, final List<Parameter> forSql, int maxRows)
			throws SQLException, QueryDaoException, IOException,
			TemplateException {
		final String sqlQuery;
		if (model.isEmpty()) {
			sqlQuery = query.getValue();
		} else {
			sqlQuery = createQuery(query.getName(), query.getValue(), model);
		}
		LOGGER.info("sqlQuery {}", sqlQuery);
		final Statement what;
		if (forSql.isEmpty()) {
			what = connection.createStatement();
			// execution de la requete
			what.execute(sqlQuery);
		} else {
			what = createStatementAndExecute(sqlQuery, model, map, connection,
					forSql);
		}
		what.setMaxRows(maxRows);
		return what;
	}

	private PreparedStatement createStatementAndExecute(final String sqlQuery,
			final Map<String, String> model, final Map<String, String> map,
			final Connection connection, final List<Parameter> forSql)
			throws SQLException, QueryDaoException {
		// on prepare la requete
		final PreparedStatement preparatemnetS = connection
				.prepareStatement(sqlQuery);
		int i = 1;
		// on injecte les valeurs des parametres
		for (final Parameter parameter : forSql) {
			final String foundValue = foundValue(parameter,
					map.get(parameter.getName()));
			if (parameter.getAssociate() == null
					||!StringUtils.isEmpty(model.get(parameter.getAssociate()))) {
				preparatemnetS.setObject(i++, foundValue);
			}

		}
		preparatemnetS.execute();
		return preparatemnetS;
	}

	private String createQuery(final String name, final String value,
			final Map<String, String> mapValue) throws IOException, TemplateException {
		final Template template = new Template(name, new StringReader(value),
				configuration);
		final StringWriter output = new StringWriter();
		template.process(mapValue, output);
		return output.toString();
	}

	/**
	 * Ferme tout.
	 * 
	 * @param connection
	 *            la connexion
	 * @param preparatemnetS
	 *            la requete
	 * @param resultSet
	 *            le resultat
	 */
	private void closeAll(final Connection connection,
			final Statement preparatemnetS, final ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (final SQLException e) {
		}
		try {
			if (preparatemnetS != null) {
				preparatemnetS.close();
			}
		} catch (final SQLException e) {
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (final SQLException e) {
		}

	}

	/**
	 * @param resultSet
	 *            ligne
	 * @return le resultat de la requete
	 * @throws SQLException
	 */
	private Row create(final ResultSet resultSet) throws SQLException {
		final List<Object> result = Lists.newArrayList();
		final ResultSetMetaData metadata = resultSet.getMetaData();
		final int columnCount = metadata.getColumnCount();
		for (int i = 1; i <= columnCount; i++) {
			result.add(resultSet
					.getObject(i));
		}
		final Row row = new Row();
		row.setColumn(result);
		return row;
	}

	/**
	 * @param parameter
	 *            la conf pour le parametre
	 * @param value
	 *            la valeur du parametre
	 * @return Si la valeur est definie la valeur sinon la valeur par defaut.
	 * @throws QueryDaoException
	 *             cas ou on ne peut determiner la valeur du parametre (list
	 *             nulle, parametre sans contenue et isNull à false)
	 */
	private String foundValue(final Parameter parameter, final String value)
			throws QueryDaoException {
		LOGGER.info("parameter {} value {}", parameter, value);
		final String resultValue;
		if (value != null) {
			resultValue = value;
		} else {
			if (parameter.getContent() != null) {
				resultValue = parameter.getContent();
			} else {
				resultValue = null;
			}
		}
		return resultValue;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
}
